package com.abss.vote.mapper;

import com.abss.vote.model.*;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.stereotype.Repository;

import java.util.List;

@Mapper
@Repository
public interface WorkMapper {

    int addSound(UserWorkVo userWorkVo);

    int addVideo(UserWorkVo userWorkVo);

    int getWorksCount(int ACTIVITY_ID);

    int getWorksCounts(@Param("ACTIVITY_ID")int ACTIVITY_ID,@Param("DISTRICT_ID")int DISTRICT_ID);

    int getAuditWorksCount(@Param("ACTIVITY_ID")int ACTIVITY_ID,@Param("DISTRICT_ID")int DISTRICT_ID);

    int getSubmitWorksCount(@Param("ACTIVITY_ID")int ACTIVITY_ID,@Param("DISTRICT_ID")int DISTRICT_ID);

    int getFinalWorksCount(@Param("ACTIVITY_ID")int ACTIVITY_ID,@Param("DISTRICT_ID")int DISTRICT_ID);
    int getPinalWorksCount(@Param("ACTIVITY_ID")int ACTIVITY_ID,@Param("DISTRICT_ID")int DISTRICT_ID);

    List<Work> getWorksVotes(@Param("ACTIVITY_ID")int ACTIVITY_ID,@Param("DISTRICT_ID")int DISTRICT_ID);

    List<UserWorkVo> getWorks(int ACTIVITY_ID);
    List<UserWorkVo> getWorkss(@Param("ACTIVITY_ID")int ACTIVITY_ID,@Param("DISTRICT_ID")int DISTRICT_ID);

    List<Work> getUserWorks(UserWorkVo userWorkVo);

    int updateSound(UserWorkVo userWorkVo);

    int updateVideo(UserWorkVo userWorkVo);


    List<UserWorkVo> getWorkDowmLoad(UserWorkVo userWorkVo);

    DistrictListUserWorkVo getWorkDetails(int WORK_ID);

    List<UserWorkVo> getWorkByActivityId(@Param("DISTRICT_ID") int DISTRICT_ID,@Param("AUDIT_STATE")  int AUDIT_STATE, @Param("WORK_NAME") String WORK_NAME,@Param("ACTIVITY_ID")  int ACTIVITY_ID);

    List<UserWorkVo> getWorkByActivityIds(@Param("AUDIT_STATE")  int AUDIT_STATE, @Param("WORK_NAME") String WORK_NAME,@Param("ACTIVITY_ID")  int ACTIVITY_ID,@Param("districtId")  int districtId);

    List<UserWorkVo> getWorkByActivityIdssa(@Param("DISTRICT_ID") int DISTRICT_ID,@Param("WORK_NAME") String WORK_NAME,@Param("ACTIVITY_ID")  int ACTIVITY_ID);
    List<UserWorkVo> getWorkByActivityIdss( @Param("WORK_NAME") String WORK_NAME,@Param("ACTIVITY_ID")  int ACTIVITY_ID);

    List<UserWorkVo> getWorkByActivityIdsssaa(@Param("DISTRICT_ID") int DISTRICT_ID,@Param("WORK_NAME") String WORK_NAME,@Param("ACTIVITY_ID")  int ACTIVITY_ID);
    List<UserWorkVo> getWorkByActivityIdsssa(@Param("DISTRICT_ID") int DISTRICT_ID,@Param("WORK_NAME") String WORK_NAME,@Param("ACTIVITY_ID")  int ACTIVITY_ID);
    List<UserWorkVo> getWorkByActivityIdsss( @Param("WORK_NAME") String WORK_NAME,@Param("ACTIVITY_ID")  int ACTIVITY_ID);
    List<UserWorkVo> getWorkByActivityIdssss( @Param("WORK_NAME") String WORK_NAME,@Param("ACTIVITY_ID")  int ACTIVITY_ID);


    List<UserWorkVo> getWorkDowmLoads(UserWorkVo userWorkVo);

    UserWorkVo getWork(int ID);

    List<AuditFailComments> getFail(String WORK_TYPE);

    int audit(@Param("AUDIT_STATE") int AUDIT_STATE,@Param("WORK_ID")  int WORK_ID,@Param("COMMENT") String COMMENT,@Param("CURRENT_STAGE") int CURRENT_STAGE);

    List<DistrictListUserWorkVo> getWorksByActivityID(int ACTIVITY_ID);

    List<UserWorkVo> WorkDowmLoad(int WORK_ID);

    int delWork(int WORK_ID);

    List<UserWorkVo> getWorkByUserId(@Param("USER_ID") String USER_ID,@Param("ACTIVITY_ID") int ACTIVITY_ID);




    @Select("select t.*,r.USER_NAME,d.DISTRICT_NAME from( select *,row_number() over(order by random) row from( select  *, newid() random from   WORK where ACTIVITY_ID=${activity_id} and DELETED=0 and CURRENT_STAGE=#{currentStage} ) AS t LEFT JOIN USER_REG_INFO r ON t.USER_ID=r.USER_ID and t.ACTIVITY_ID=r.ACTIVITY_ID and r.DELETED=0 LEFT JOIN DISTRICT_LIST d on r.DISTRICT_ID=d.ID WHERE t.ROW between ${start} and ${end}")
    List<Work> getWorksOrderByRandom(@Param("activity_id")int activity_id,@Param("start")int start,@Param("end")int end,@Param("currentStage") int currentStage);

//没有条件的审核通过前
    @Select("SELECT t.*,r.USER_NAME,d.DISTRICT_NAME FROM( select * from WORK where ACTIVITY_ID=${activity_id}  and DELETED=0 and CURRENT_STAGE in (0,1)  ) AS t LEFT JOIN USER_REG_INFO r ON t.USER_ID=r.USER_ID and t.ACTIVITY_ID=r.ACTIVITY_ID and r.DELETED=0 LEFT JOIN DISTRICT_LIST d on r.DISTRICT_ID=d.ID")
    List<Work> getWorksAudit(@Param("activity_id")int activity_id,@Param("start")int start,@Param("end")int end);
//没有条件的提交的作品
    @Select("SELECT t.*,r.USER_NAME,d.DISTRICT_NAME FROM( select * from WORK where ACTIVITY_ID=${activity_id}  and DELETED=0 and CURRENT_STAGE=2  ) AS t LEFT JOIN USER_REG_INFO r ON t.USER_ID=r.USER_ID and t.ACTIVITY_ID=r.ACTIVITY_ID and r.DELETED=0 LEFT JOIN DISTRICT_LIST d on r.DISTRICT_ID=d.ID")
    List<Work> getWorksSubmit(@Param("activity_id")int activity_id,@Param("start")int start,@Param("end")int end);
//没有条件的晋级的作品
    @Select("SELECT t.*,r.USER_NAME,d.DISTRICT_NAME FROM( select * from WORK where ACTIVITY_ID=${activity_id}  and DELETED=0 and CURRENT_STAGE>=3  ) AS t LEFT JOIN USER_REG_INFO r ON t.USER_ID=r.USER_ID and t.ACTIVITY_ID=r.ACTIVITY_ID and r.DELETED=0 LEFT JOIN DISTRICT_LIST d on r.DISTRICT_ID=d.ID")
    List<Work> getWorksPromotion(@Param("activity_id")int activity_id,@Param("start")int start,@Param("end")int end);



















    @Select("SELECT t.*,r.USER_NAME,d.DISTRICT_NAME FROM( select *,row_number() over(order by WORK_ID DESC) ROW from WORK where ACTIVITY_ID=${activity_id} and DELETED=0 and CURRENT_STAGE=#{currentStage} ) AS t LEFT JOIN USER_REG_INFO r ON t.USER_ID=r.USER_ID and t.ACTIVITY_ID=r.ACTIVITY_ID and r.DELETED=0 LEFT JOIN DISTRICT_LIST d on r.DISTRICT_ID=d.ID WHERE t.ROW between ${start} and ${end}")
    List<Work> getWorksOrderByNewUpload(@Param("activity_id")int activity_id,@Param("start")int start,@Param("end")int end,@Param("currentStage") int currentStage);





    @Select("select t.*,r.USER_NAME,d.DISTRICT_NAME from( select *,row_number() over(order by random) row from( select  *, newid() random from   WORK where ACTIVITY_ID=${activity_id} and CURRENT_STAGE=#{currentStage}   and DELETED=0 and WORK_NAME like '%${search_content}%') AS t LEFT JOIN USER_REG_INFO r ON t.USER_ID=r.USER_ID and t.ACTIVITY_ID=r.ACTIVITY_ID and r.DELETED=0 LEFT JOIN DISTRICT_LIST d on r.DISTRICT_ID=d.ID WHERE d.ID=${district_id} and t.ROW between ${start} and ${end}")
    List<Work> getSearchedWorksOrderByRandom(@Param("district_id")int district_id,@Param("search_content")String search_content, @Param("activity_id")int activity_id, @Param("start")int start, @Param("end")int end,@Param("currentStage") int currentStage);

    @Select("SELECT t.*,r.USER_NAME,d.DISTRICT_NAME FROM( select *,row_number() over(order by WORK_ID DESC) ROW from WORK where ACTIVITY_ID=${activity_id} and CURRENT_STAGE=#{currentStage} and DELETED=0 and WORK_NAME like '%${search_content}%' ) AS t LEFT JOIN USER_REG_INFO r ON t.USER_ID=r.USER_ID and t.ACTIVITY_ID=r.ACTIVITY_ID and r.DELETED=0 LEFT JOIN DISTRICT_LIST d on r.DISTRICT_ID=d.ID WHERE d.ID=${district_id} and t.ROW between ${start} and ${end}")
    List<Work> getSearchedWorksOrderByNewUpload(@Param("district_id")int district_id,@Param("search_content")String search_content,@Param("activity_id") int activity_id, @Param("start")int start, @Param("end")int end,@Param("currentStage") int currentStage);





    @Select("SELECT t.*,r.USER_NAME,d.DISTRICT_NAME FROM( select * from WORK where ACTIVITY_ID=${activity_id} and CURRENT_STAGE in (0,1) and DELETED=0 and WORK_NAME like '%${search_content}%' ) AS t LEFT JOIN USER_REG_INFO r ON t.USER_ID=r.USER_ID and t.ACTIVITY_ID=r.ACTIVITY_ID and r.DELETED=0 LEFT JOIN DISTRICT_LIST d on r.DISTRICT_ID=d.ID")
    List<Work> getSearchedWorksOrderByVotesNumOneAudit(@Param("search_content")String search_content, @Param("activity_id")int activity_id, @Param("start")int start,@Param("end") int end);
    @Select("SELECT t.*,r.USER_NAME,d.DISTRICT_NAME FROM( select * from WORK where ACTIVITY_ID=${activity_id} and CURRENT_STAGE=2 and DELETED=0 and WORK_NAME like '%${search_content}%' ) AS t LEFT JOIN USER_REG_INFO r ON t.USER_ID=r.USER_ID and t.ACTIVITY_ID=r.ACTIVITY_ID and r.DELETED=0 LEFT JOIN DISTRICT_LIST d on r.DISTRICT_ID=d.ID")
    List<Work> getSearchedWorksOrderByVotesNumOneSubmit(@Param("search_content")String search_content, @Param("activity_id")int activity_id, @Param("start")int start,@Param("end") int end);
    @Select("SELECT t.*,r.USER_NAME,d.DISTRICT_NAME FROM( select * from WORK where ACTIVITY_ID=${activity_id} and CURRENT_STAGE>=3 and DELETED=0 and WORK_NAME like '%${search_content}%' ) AS t LEFT JOIN USER_REG_INFO r ON t.USER_ID=r.USER_ID and t.ACTIVITY_ID=r.ACTIVITY_ID and r.DELETED=0 LEFT JOIN DISTRICT_LIST d on r.DISTRICT_ID=d.ID")
    List<Work> getSearchedWorksOrderByVotesNumOnePromotion(@Param("search_content")String search_content, @Param("activity_id")int activity_id, @Param("start")int start,@Param("end") int end);



    @Select("select * from ( SELECT t.*,r.DISTRICT_ID,r.USER_NAME,d.DISTRICT_NAME FROM( select * from WORK where ACTIVITY_ID=${activity_id} and CURRENT_STAGE in (0,1) and DELETED=0) AS t LEFT JOIN USER_REG_INFO r ON t.USER_ID=r.USER_ID and t.ACTIVITY_ID=r.ACTIVITY_ID and r.DELETED=0 LEFT JOIN DISTRICT_LIST d on r.DISTRICT_ID=d.ID WHERE r.DISTRICT_ID=${district_id} ) s ")
    List<Work> getSearchedWorksOrderByVotesNumTwoAudit(@Param("district_id")int district_id,@Param("activity_id")int activity_id, @Param("start")int start,@Param("end") int end);
    @Select("select * from ( SELECT t.*,r.DISTRICT_ID,r.USER_NAME,d.DISTRICT_NAME FROM( select * from WORK where ACTIVITY_ID=${activity_id} and CURRENT_STAGE=2 and DELETED=0) AS t LEFT JOIN USER_REG_INFO r ON t.USER_ID=r.USER_ID and t.ACTIVITY_ID=r.ACTIVITY_ID and r.DELETED=0 LEFT JOIN DISTRICT_LIST d on r.DISTRICT_ID=d.ID WHERE r.DISTRICT_ID=${district_id} ) s")
    List<Work> getSearchedWorksOrderByVotesNumTwoSubmit(@Param("district_id")int district_id,@Param("activity_id")int activity_id, @Param("start")int start,@Param("end") int end);
    @Select("select * from ( SELECT t.*,r.DISTRICT_ID,r.USER_NAME,d.DISTRICT_NAME FROM( select * from WORK where ACTIVITY_ID=${activity_id} and CURRENT_STAGE>=3 and DELETED=0) AS t LEFT JOIN USER_REG_INFO r ON t.USER_ID=r.USER_ID and t.ACTIVITY_ID=r.ACTIVITY_ID and r.DELETED=0 LEFT JOIN DISTRICT_LIST d on r.DISTRICT_ID=d.ID WHERE r.DISTRICT_ID=${district_id} ) s ")
    List<Work> getSearchedWorksOrderByVotesNumTwoPromotion(@Param("district_id")int district_id,@Param("activity_id")int activity_id, @Param("start")int start,@Param("end") int end);




    @Select("select * from ( SELECT t.*,r.DISTRICT_ID,r.USER_NAME,d.DISTRICT_NAME FROM( select * from WORK where ACTIVITY_ID=${activity_id} and CURRENT_STAGE in (0,1) and DELETED=0 and WORK_NAME like '%${search_content}%') AS t LEFT JOIN USER_REG_INFO r ON t.USER_ID=r.USER_ID and t.ACTIVITY_ID=r.ACTIVITY_ID and r.DELETED=0 LEFT JOIN DISTRICT_LIST d on r.DISTRICT_ID=d.ID WHERE r.DISTRICT_ID=${district_id} ) s ")
    List<Work> getSearchedWorksOrderByVotesNumAudit(@Param("district_id")int district_id,@Param("search_content")String search_content, @Param("activity_id")int activity_id, @Param("start")int start,@Param("end") int end);
    @Select("select * from ( SELECT t.*,r.DISTRICT_ID,r.USER_NAME,d.DISTRICT_NAME FROM( select * from WORK where ACTIVITY_ID=${activity_id} and CURRENT_STAGE=2 and DELETED=0 and WORK_NAME like '%${search_content}%') AS t LEFT JOIN USER_REG_INFO r ON t.USER_ID=r.USER_ID and t.ACTIVITY_ID=r.ACTIVITY_ID and r.DELETED=0 LEFT JOIN DISTRICT_LIST d on r.DISTRICT_ID=d.ID WHERE r.DISTRICT_ID=${district_id} ) s ")
    List<Work> getSearchedWorksOrderByVotesNumSubmit(@Param("district_id")int district_id,@Param("search_content")String search_content, @Param("activity_id")int activity_id, @Param("start")int start,@Param("end") int end);
    @Select("select * from ( SELECT t.*,r.DISTRICT_ID,r.USER_NAME,d.DISTRICT_NAME FROM( select * from WORK where ACTIVITY_ID=${activity_id} and CURRENT_STAGE>=3 and DELETED=0 and WORK_NAME like '%${search_content}%') AS t LEFT JOIN USER_REG_INFO r ON t.USER_ID=r.USER_ID and t.ACTIVITY_ID=r.ACTIVITY_ID and r.DELETED=0 LEFT JOIN DISTRICT_LIST d on r.DISTRICT_ID=d.ID WHERE r.DISTRICT_ID=${district_id} ) s ")
    List<Work> getSearchedWorksOrderByVotesNumPromotion(@Param("district_id")int district_id,@Param("search_content")String search_content, @Param("activity_id")int activity_id, @Param("start")int start,@Param("end") int end);









    @Select("select t.*,r.USER_NAME,d.DISTRICT_NAME from( select * from  WORK where WORK_ID=${work_id} and DELETED=0) AS t LEFT JOIN USER_REG_INFO r ON t.USER_ID=r.USER_ID and t.ACTIVITY_ID=r.ACTIVITY_ID and r.DELETED=0 LEFT JOIN DISTRICT_LIST d on r.DISTRICT_ID=d.ID and r.DELETED=0")
    Work getWorkInfoById(@Param("work_id")int work_id);


    int delWorks(@Param("user_id")String user_id,@Param("activity_id") int activity_id);

    int addyinWork(Work work);

    int updSound(Work work);
    int updSounds(Work work);



    Work getWORKS(int work_id);


    @Select("select * from WORK where USER_ID=#{user_id} and ACTIVITY_ID=#{activity_id} and DELETED=0")
    List<Work> getWorksByUserIdAndActivityId(@Param("user_id")String user_id,@Param("activity_id") int activity_id);

    int getWorkisempty(@Param("user_id")String user_id,@Param("activity_id") int activity_id);

    int setWork(int WORK_ID);
    int deWork(int WORK_ID);

    @Select("select * from ARTICLE where DELETED=0")
    List<Article> getReadWork();

    @Select("select * from ARTICLE where ID=#{ID}")
    Article getActicleById(int ID);

    int addRecorderWork(Work work);

    int createWork(UserWorkVo userWorkVo);

    int promotionWork(int WORK_ID);

    int deWpromotionWorkork(int work_id);

    String getPicName(int work_id);

    int backEditorWork(Work work);


    int setVisit(int work_id);

    int getAnumber(int activity_id);

    int getSnumber(int activity_id);


    int getFnumber(int activity_id);
    int getPnumber(int activity_id);

    List<UserWorkVo> getWorkByActivityIdsExport(@Param("activity_id") int activity_id,@Param("districtId") int districtId);
    List<UserWorkVo> getWorkByActivityIdsExports(@Param("activity_id") int activity_id);
    List<UserWorkVo> getWorkByActivityIdsExportss(@Param("activity_id") int activity_id);
    List<UserWorkVo> getWorkByActivityIdsExportsss(@Param("activity_id") int activity_id);

    List<UserWorkVo> getWorkByActivityIdExport(@Param("activity_id") int activity_id);
    List<UserWorkVo> getWorkByActivityIdExporta(@Param("activity_id") int activity_id);
    List<UserWorkVo> getWorkByActivityIdExportaa(@Param("activity_id") int activity_id);
    List<UserWorkVo> getWorkByActivityIdExportaaa(@Param("activity_id") int activity_id);

    List<UserWorkVo> getWorkByActivityIdExportOne(@Param("id") int id);

    UserWorkVo getWorkDetail(@Param("work_id") int work_id);

    int updateWorkName(UserWorkVo work);

    int updateWork(UserWorkVo work);

    @Update("update ARTICLE set DELETED=1 where ID=#{id}")
    int deleteArticle(@Param("id") Integer id);

    int updateArticle(Article article);

    int addArticle(Article article);

    List<Article> getReadWorks(@Param("name")  String name);

    @Select("select * from ARTICLE where ID=#{id}")
    Article getArticle(@Param("id") Integer id);


    @Select("SELECT w.* from [WORK] w LEFT JOIN ACTIVITY a on w.ACTIVITY_ID=a.ACTIVITY_ID where a.REMOVE=0 and w.DELETED=0")
    List<Work> getAllWork();

    @Update("update WORK set WORK_VISIST=#{work_visist} where WORK_ID=#{workId}")
    int setWORK_VISIST(@Param("workId") Integer workId,@Param("work_visist")Integer work_visist);

    @Update("update WORK set VOTES_NUMBER=#{work_votes} where WORK_ID=#{workId}")
    int setWORK_VOTES(@Param("workId") Integer workId,@Param("work_votes") Integer work_votes);

    UserWorkVo getWorksss(int work_id);


    int setWorkPrize(Prize prize);

    int delWorkPrize(Prize prize);

    void setWorkPrizeDelete(@Param("prizeId")Integer prizeId);

    @Update("update WORK set IS_AWARD=1 where WORK_ID=#{work_id}")
    int award(@Param("work_id")int work_id);

    @Select("SELECT * from PRIZE_USER where ACTIVITY_ID=#{activity_id}")
    PrizeUser getPrizeUser(@Param("activity_id")int activity_id);

    int insertPrizeUser(PrizeUser prizeUser);

    int updatePrizeUser(PrizeUser prizeUser);

    List<UserWorkVo> getWorkByPrizeId(@Param("prize_id")Integer prize_id);

    List<Work> getSearchedWorksOrderByVotesNumOneAuditQ(@Param("search_user_name")String search_user_name,@Param("search_work_id") Integer search_work_id,@Param("search_content") String search_content,@Param("activity_id") int activity_id,@Param("district_id") int district_id);

    List<Work> getSearchedWorksOrderByVotesNumOneSubmitQ(@Param("search_user_name")String search_user_name,@Param("search_work_id") Integer search_work_id,@Param("search_content") String search_content,@Param("activity_id") int activity_id,@Param("district_id") int district_id);

    List<Work> getSearchedWorksOrderByVotesNumOnePromotionQ(@Param("search_user_name")String search_user_name,@Param("search_work_id") Integer search_work_id,@Param("search_content") String search_content,@Param("activity_id") int activity_id,@Param("district_id") int district_id);
}
